﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;

using BMS.Models;
using BMS.Utilities;

namespace BMS.Models.DataAccess
{
    public class ClientRepository
    {
        private DatabaseAccess db = new DatabaseAccess();

        public List<ClientModel> GetAllClients()
        {
            List<ClientModel> allClients = new List<ClientModel>();
            String query = "SELECT * FROM " + Configuration.DatabaseName + ".client";

            db.DBConnectionOn();

            try
            {
                MySqlCommand cmd = new MySqlCommand(query, db.connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    ClientModel client = new ClientModel();

                    client.ClientNo = dataReader["vc_fldClientNo"] + "";
                    client.ClientName = dataReader["vc_fldClientName"] + "";
                    client.Address1 = dataReader["vc_fldAddress1"] + "";
                    client.Address2 = dataReader["vc_fldAddress2"] + "";
                    client.Address3 = dataReader["vc_fldAddress3"] + "";
                    client.Address4 = dataReader["vc_fldAddress4"] + "";
                    client.Address5 = dataReader["vc_fldAddress5"] + "";
                    client.TelephoneNo = dataReader["vc_fldTelephoneNo"] + "";
                    client.FaxNo = dataReader["vc_fldFaxNo"] + "";
                    client.AttentionTo = dataReader["vc_fldAttentionTo"] + "";
                    client.FMName = dataReader["vc_fldFMName"] + "";
                    client.CompanyName = dataReader["vc_fldCompanyName"] + "";
                    client.SubContName = dataReader["vc_fldSubContName"] + "";
                    client.ClientCreationDate = DateTime.Parse(dataReader["dt_fldCreate"] + "");

                    allClients.Add(client);
                }
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                db.DBConnectionOff();
            }
            return allClients;
        }

        public ClientModel GetClient(string FMName)
        {
            ClientModel foundClient = new ClientModel();

            List<ClientModel> clientModelList = GetAllClients();
            foundClient = clientModelList.Find(x => x.FMName == FMName);

            return foundClient;
        }

        public bool Insert(ClientModel clientModel)
        {
            bool isInserted = false;

            try
            {
                String query = "INSERT INTO " + Configuration.DatabaseName + ".client (vc_fldClientNo, vc_fldClientName, vc_fldAddress1, vc_fldAddress2,"
                    + "vc_fldAddress3, vc_fldAddress4, vc_fldAddress5, vc_fldTelephoneNo, vc_fldFaxNo, vc_fldAttentionTo, vc_fldFMName, vc_fldCompanyName," 
                    + "vc_fldSubContName, dt_fldCreate) VALUES('" + clientModel.ClientNo + "', '" + clientModel.ClientName + "', '" + clientModel.Address1 + "', '"
                    + clientModel.Address2 + "', '" + clientModel.Address3 + "', '" + clientModel.Address4 + "', '" + clientModel.Address5 + "', '"
                    + clientModel.TelephoneNo + "', '" + clientModel.FaxNo + "', '" + clientModel.AttentionTo + "', '" + clientModel.FMName + "', '"
                    + clientModel.CompanyName + "', '" + clientModel.SubContName + "', '" + clientModel.ClientCreationDate.ToString("yyyy-MM-dd H:mm:ss") + "')";
                db.DBConnectionOn();
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, db.connection);

                //Execute command
                cmd.ExecuteNonQuery();

                isInserted = true;
            }
            catch (Exception e)
            {
                isInserted = false;
                throw;
            }
            finally
            {
                db.DBConnectionOff();
            }

            return isInserted;
        }
    }
}